package com.hnkywl.sts.dao.sys;

import com.hnkywl.sts.entity.sys.Gg;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.hibernate.SQLQuery;
import org.hibernate.tool.hbm2x.StringUtils;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

/**
 * 说明：
 * 包:        com.hnkywl.sts.dao.sys
 * 类描述:    规格dao类
 * 创建人:    ccr
 * 创建时间: 13-2-18 上午16:09
 * 修改人:    ccr
 * 修改时间: 13-2-18 上午16:09
 * 修改备注: [说明本次修改内容]
 * 版本:        [v1.0]
 */
@Repository
public class GgDao extends SimpleHibernateTemplate<Gg, Long> {
    /**
     * 方法名:     get
     * 功能描述:   根据属性查找所有规格并分页显示
     * 输入参数:   @param name 物资名称
     * code 规格代码
     * ggxh 规格型号
     * ggjc 规格简称
     * pmId 品名Id
     * pageNo 分页组件参数，当前页
     * pageSize 分页组件参数 页面展示的数量
     * 返回值:     @return Page<Pm> 规格的分页组件
     * 异常:      <按照异常名字的字母顺序>
     * 创建人:     ccr
     * 创建日期:   13-2-19 下午16:07
     * 修改人:     ccr
     * 修改日期:   13-2-19 下午16:07
     */
    public Page<Gg> get(String type, String name, String code, Long pmId, int pageNo, int pageSize) {
        String bmylz = type != null && type.equals("bg") ? "bmylz2" : "bmylz3";//"jy"经营 "bg"保供
        Page<Gg> page = new Page<Gg>(pageNo, pageSize, 0);
        String select = "select wzbm.id as \"id\",wzbm.name as \"name\",bm.@bmylz as \"code\",yjbm.name as \"yjwzbm.name\",4 as \"level\" ";
        String from = "from t_sys_yjwzbm wzbm left join t_sys_pm pm on(wzbm.yjwzbm_id = pm.id) left join t_sys_bmb bm on(wzbm.code = bm.bmz)";
        StringBuilder where = new StringBuilder();
        where.append("bm.zt = 1 and bm.@bmylz is not null");
        if (StringUtils.isNotEmpty(name)) {
            if (where.length() != 0)
                where.append(" and ");
            where.append("wzbm.name like :name");
        }
        if (StringUtils.isNotEmpty(code)) {
            if (where.length() != 0)
                where.append(" and ");
            where.append("bm.@bmylz = :name");
        }
        if (pmId != null) {
            if (where.length() != 0)
                where.append(" and ");
            where.append("pm.id = :pmId");
        }
        String orderBy = " order by bm.@bmylz asc";
        String count = "select count(1) " + from + (where.length() != 0 ? " where " : "") + where;
        String sql = select + from + (where.length() != 0 ? " where " : "") + where + orderBy;
        count = count.replaceAll("@bmylz", bmylz);
        sql = sql.replaceAll("@bmylz", bmylz);
        SQLQuery sqlQuery = getSession().createSQLQuery(sql);
        SQLQuery countQuery = getSession().createSQLQuery(count);
        if (StringUtils.isNotEmpty(name)) {
            countQuery.setString("name", "'%" + name.trim() + "%'");
            sqlQuery.setString("name", "'%" + name.trim() + "%'");
        }
        if (StringUtils.isNotEmpty(code)) {
            countQuery.setString("code", code);
            sqlQuery.setString("code", code);
        }
        if (pmId != null) {
            countQuery.setLong("pmId", pmId);
            sqlQuery.setLong("pmId", pmId);
        }
        page.setTotalCount(((BigDecimal) countQuery.uniqueResult()).intValue());
        if (page.isFirstSetted())
            sqlQuery.setFirstResult(page.getFirst());
        if (page.isPageSizeSetted())
            sqlQuery.setMaxResults(page.getPageSize());
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        page.setResult(sqlQuery.list());
        return page;
    }

    public Page<Gg> findGridJson(Long sjwzbmId, int pageNo, int pageSize) {
        Page<Gg> page = new Page<Gg>(pageNo, pageSize, 0);
        String select = "select wzbm.id as \"id\",wzbm.name as \"name\",bm.bmz as \"code\",wzbm.oldcode as \"oldcode\",sjbm.name as \"pm_name\"," +
                "wzbm.ggxh as \"ggxh\",wzbm.ggjc as \"ggjc\",wzbm.th as \"th\",wzbm.cz as \"cz\",wzbm.dw as \"dw\",wzbm.sfbj as \"sfbj\",bm.bmylz2 as \"bgCode\" ";
        String from = " from t_sys_gg wzbm left join t_sys_bmb bm on(wzbm.code = bm.bmz) " +
                "left join t_sys_bmlx bmlx on(bm.bmlxid = bmlx.id) " +
                "left join t_sys_pm sjbm on(wzbm.pm_id = sjbm.id)";
        StringBuilder where = new StringBuilder();
        where.append("bm.bmz is not null and lower(bmlx.lxzjm) = 'gg' and bm.zt = '1'");
        if (sjwzbmId != null) {
            if (where.length() != 0)
                where.append(" and ");
            where.append("sjbm.id = :sjbmId");
        }
        String orderBy = " order by wzbm.name,wzbm.cz,wzbm.ggxh asc";
        String queryString = select + from + (where.length() != 0 ? " where " : "") + where + orderBy;
        String countString = "select count(1) " + from + (where.length() != 0 ? " where " : "") + where;
        SQLQuery query = getSession().createSQLQuery(queryString);
        SQLQuery count = getSession().createSQLQuery(countString);
        if (sjwzbmId != null && sjwzbmId != 0) {
            query.setLong("sjbmId", sjwzbmId);
            count.setLong("sjbmId", sjwzbmId);
        }
        if (page.isFirstSetted())
            query.setFirstResult(page.getFirst());
        if (page.isPageSizeSetted())
            query.setMaxResults(page.getPageSize());
        page.setTotalCount(((BigDecimal) count.uniqueResult()).intValue());
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        page.setResult(query.list());
        return page;
    }

//    public Page<Gg> search(String pmName,String name,String code,int pageNo,int pageSize) {
//        Page<Gg> ggPage = new Page<Gg>(pageNo,pageSize,0);
//        ggPage.setAutoCount(true);
//        ggPage.setOrder("asc");
//        ggPage.setOrderBy("code");
//        Criteria criteria = getSession().createCriteria(this.entityClass)
//                .createAlias("pm", "pm", CriteriaSpecification.LEFT_JOIN)
//                .setProjection(Projections.projectionList()
//                        .add(Property.forName("id").as("id"))
//                        .add(Projections.sqlProjection("this_.name as name", new String[]{"name"}, new Type[]{Hibernate.STRING}))
//                        .add(Projections.sqlProjection("this_.code as code", new String[]{"code"}, new Type[]{Hibernate.STRING}))
//                        .add(Property.forName("ggxh").as("ggxh"))
//                        .add(Property.forName("ggjc").as("ggjc"))
//                        .add(Property.forName("th").as("th"))
//                        .add(Property.forName("dw").as("dw"))
//                        .add(Property.forName("sfbj").as("sfbj"))
//                        .add(Projections.sqlProjection("pm1_.name as pm_name", new String[]{"pm_name"}, new Type[]{Hibernate.STRING}))
//                        .add(Property.forName("cz").as("cz")))
//                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
//        if(StringUtils.isNotEmpty(pmName)){
//            criteria.add(Restrictions.like("pm.name",pmName, MatchMode.ANYWHERE));
//        }
//        if(StringUtils.isNotEmpty(name))
//            criteria.add(Property.forName("name").like(name,MatchMode.START));
//        if(StringUtils.isNotEmpty(code))
//            criteria.add(Property.forName("code").like(code,MatchMode.START));
//        return findByCriteria(ggPage,criteria);
//    }

    public Page<Gg> search(Long gsId, String pmName, String name, String code, String cz, String ggxh, int pageNo, int pageSize) {
        Page<Gg> page = new Page<Gg>(pageNo, pageSize, 0);
        String select = "select ggid as \"id\",th as \"th\", name as \"name\",code as \"code\",oldcode as \"oldcode\",ggxh as \"ggxh\",cz as \"cz\",ggjc as \"ggjc\",dw as \"dw\",pm_name as \"pm_name\"";
        String from = " from v_wzbm_gg_search gg left join t_sys_bmb bm on(gg.code = bm.bmz) left join t_sys_bmlx bmlx on(bm.bmlxid = bmlx.id)";
        StringBuilder where = new StringBuilder();
        where.append("bm.zt = '1' and lower(bmlx.lxzjm) = 'gg'");
        if (gsId != null) {
            if (where.length() != 0)
                where.append(" and ");
            where.append("gsid = :gsId");
        }
        if (StringUtils.isNotEmpty(pmName)) {
            if (where.length() != 0)
                where.append(" and ");
            where.append("pm_name like :pmName");
        }
        if (StringUtils.isNotEmpty(name)) {
            if (where.length() != 0)
                where.append(" and ");
            where.append("name like :name");
        }
        if (StringUtils.isNotEmpty(code)) {
            if (where.length() != 0)
                where.append(" and ");
            where.append("(oldcode like :oldcode or code like :oldcode)");
        }
        if (StringUtils.isNotEmpty(cz)) {
            if (where.length() != 0)
                where.append(" and ");
            where.append("cz like :cz");
        }
        if (StringUtils.isNotEmpty(ggxh)) {
            if (where.length() != 0)
                where.append(" and ");
            where.append("ggxh like :ggxh");
        }
        String orderBy = " order by gg.name,gg.cz,gg.ggxh asc";
        SQLQuery query = getSession().createSQLQuery(select + from + (where.length() != 0 ? " where " : "") + where + orderBy);
        SQLQuery count = getSession().createSQLQuery("select count(1) " + from + (where.length() != 0 ? " where " : "") + where + orderBy);
        if (gsId != null) {
            query.setLong("gsId", gsId);
            count.setLong("gsId", gsId);
        }
        if (StringUtils.isNotEmpty(pmName)) {
            query.setString("pmName", "%" + pmName + "%");
            count.setString("pmName", "%" + pmName + "%");
        }
        if (StringUtils.isNotEmpty(name)) {
            query.setString("name", "%" + name + "%");
            count.setString("name", "%" + name + "%");
        }
        if (StringUtils.isNotEmpty(code)) {
//            query.setString("code",code);
//            count.setString("code",code);
            query.setString("oldcode", code + "%");
            count.setString("oldcode", code + "%");
        }
        if (StringUtils.isNotEmpty(cz)) {
            query.setString("cz", "%" + cz + "%");
            count.setString("cz", "%" + cz + "%");
        }
        if (StringUtils.isNotEmpty(ggxh)) {
            query.setString("ggxh", "%" + ggxh + "%");
            count.setString("ggxh", "%" + ggxh + "%");
        }
        if (page.isFirstSetted())
            query.setFirstResult(page.getFirst());
        if (page.isPageSizeSetted())
            query.setMaxResults(page.getPageSize());
        page.setTotalCount(((BigDecimal) count.uniqueResult()).intValue());
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        page.setResult(query.list());
        return page;
    }

    public Page<Gg> searchAll(String pmName, String name, String code, String cz, String ggxh, int pageNo, int pageSize) {
        Page<Gg> page = new Page<Gg>(pageNo, pageSize, 0);
        String select = "select gg.id as \"id\",gg.th as \"th\", gg.name as \"name\",gg.code as \"code\",gg.oldcode as \"oldcode\",gg.ggxh as \"ggxh\",gg.cz as \"cz\",gg.ggjc as \"ggjc\",gg.dw as \"dw\",pm.name as \"pm_name\"";
        String from = " from t_sys_gg gg left join t_sys_bmb bm on(gg.code = bm.bmz) left join t_sys_bmlx bmlx on(bm.bmlxid = bmlx.id) left join t_sys_pm pm on pm.id = gg.pm_id";
        StringBuilder where = new StringBuilder();
        where.append("bm.zt = '1' and lower(bmlx.lxzjm) = 'gg'");
        if (StringUtils.isNotEmpty(pmName)) {
            if (where.length() != 0)
                where.append(" and ");
            where.append("pm.name like :pmName");
        }
        if (StringUtils.isNotEmpty(name)) {
            if (where.length() != 0)
                where.append(" and ");
            where.append("gg.name like :name");
        }
        if (StringUtils.isNotEmpty(code)) {
            if (where.length() != 0)
                where.append(" and ");
            where.append("(gg.oldcode like :oldcode or gg.code like :oldcode)");
        }
        if (StringUtils.isNotEmpty(cz)) {
            if (where.length() != 0)
                where.append(" and ");
            where.append("gg.cz like :cz");
        }
        if (StringUtils.isNotEmpty(ggxh)) {
            if (where.length() != 0)
                where.append(" and ");
            where.append("gg.ggxh like :ggxh");
        }
        String orderBy = " order by gg.name,gg.cz,gg.ggxh asc";
        SQLQuery query = getSession().createSQLQuery(select + from + (where.length() != 0 ? " where " : "") + where + orderBy);
        SQLQuery count = getSession().createSQLQuery("select count(1) " + from + (where.length() != 0 ? " where " : "") + where + orderBy);
        if (StringUtils.isNotEmpty(pmName)) {
            query.setString("pmName", "%" + pmName + "%");
            count.setString("pmName", "%" + pmName + "%");
        }
        if (StringUtils.isNotEmpty(name)) {
            query.setString("name", "%" + name + "%");
            count.setString("name", "%" + name + "%");
        }
        if (StringUtils.isNotEmpty(code)) {
//            query.setString("code",code);
//            count.setString("code",code);
            query.setString("oldcode", code + "%");
            count.setString("oldcode", code + "%");
        }
        if (StringUtils.isNotEmpty(cz)) {
            query.setString("cz", "%" + cz + "%");
            count.setString("cz", "%" + cz + "%");
        }
        if (StringUtils.isNotEmpty(ggxh)) {
            query.setString("ggxh", "%" + ggxh + "%");
            count.setString("ggxh", "%" + ggxh + "%");
        }
        if (page.isFirstSetted())
            query.setFirstResult(page.getFirst());
        if (page.isPageSizeSetted())
            query.setMaxResults(page.getPageSize());
        page.setTotalCount(((BigDecimal) count.uniqueResult()).intValue());
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        page.setResult(query.list());
        return page;
    }


    /**
     * 方法名:     getWzbmTreeJson
     * 功能描述:   返回规格树的Json字符串
     * 输入参数:   @param pmId 品名的Id
     * 返回值:     @return String {id:id,name:name,level:level,children:[]}
     * 异常:      <按照异常名字的字母顺序>
     * 创建人:     ccr
     * 创建日期:   13-2-19 下午17:19
     * 修改人:     ccr
     * 修改日期:   13-2-19 下午17:19
     */
    public String getWzbmTreeJson(Long pmId) {
        try {
            String nodes = "[";
            StringBuffer json = new StringBuffer();
            String hql = "from Gg where pm.id = ?";
            List<Gg> result = find(hql, pmId);
            if (result != null && !result.isEmpty()) {
                for (int i = 0; i < result.size(); i++) {
                    Gg o = result.get(i);
                    json.append("{\"id\":\"");
                    json.append(o.getId()).append(":").append("4");
                    json.append("\",\"text\":\"");
                    json.append(o.getName());
                    json.append("\",\"attributes\":{\"level\":\"4\"},");
                    json.append("\"state\":\"open\"");
                    json.append("},");
                }
                nodes += json.substring(0, json.length() - 1);
            }
            nodes += "]";
            return nodes;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "[]";
    }

    /**
     * 方法名:     checkCode
     * 功能描述:   判断规格代码是否重复
     * 输入参数:   @param id 规格的Id若为空则为新增校验，否则修改校验
     * code 规格代码
     * 返回值:     @return boolean true表示数据库中已存在，false表示数据库中不存在
     * 异常:      <按照异常名字的字母顺序>
     * 创建人:     zsp
     * 创建日期:   13-2-20 下午15:57
     * 修改人:     zsp
     * 修改日期:   13-2-20 下午15:57
     */
    public Boolean checkCode(Long id, String code) {
        String hql;
        if (id == null) {
            //修改检验
            hql = "from Gg where code = ? ";
            return this.existsQuery(hql, code);
        } else {
            //新增检验
            hql = "from Gg where code = ? and id <>?";
            return this.existsQuery(hql, code, id);
        }
    }

    /**
     * 方法名:     checkName
     * 功能描述:   判断物资名称是否重复
     * 输入参数:   @param id 规格的Id若为空则为新增校验，否则修改校验
     * code 规格代码
     * 返回值:     @return boolean true表示数据库中已存在，false表示数据库中不存在
     * 异常:      <按照异常名字的字母顺序>
     * 创建人:     zsp
     * 创建日期:   13-2-20 下午15:57
     * 修改人:     zsp
     * 修改日期:   13-2-20 下午15:57
     */
    public Boolean checkName(Long id, String name) {
        String hql;
        if (id == null) {
            //修改检验
            hql = "from Gg where name = ? ";
            return this.existsQuery(hql, name);
        } else {
            //新增检验
            hql = "from Gg where name = ? and id <>?";
            return this.existsQuery(hql, name, id);
        }
    }

    /**
     * 验证重复Gg
     *
     * @return
     */
    public Boolean checkRepeatGg(String name, String ggxh, String cz) {
        String hql = "from Gg where name = ? and ggxh = ? and cz = ?";
        return this.existsQuery(hql, name, ggxh, cz);
    }


    /**
     * 验证重复Gg
     *
     * @return
     */
    public Gg findGg(String name, String ggxh, String cz) {
        String hql = "from Gg where name = ? and ggxh = ? and cz = ?";
        List<Gg> ggList = this.find(hql, name, ggxh, cz);
        if (null != ggList && ggList.size() > 0) {
            return ggList.get(0);
        }
        return null;
    }


    public Gg findByCode(String code) {
        String hql = "select gg from Gg gg,Bm bm where gg.code = bm.bmz and gg.code = ? and bm.zt = 1 ";
        return (Gg) findUnique(hql, code);
    }

    //根据属性找出物资  //N0000679  S000004943  Q0002168
    public List<Gg> findWzbmByPmCzCode(String productCode, String specCode, String materialCode) {
        String hql = "select gg from Gg gg,Bm bm where gg.code = bm.bmz and gg.pmCode = ? and gg.ggCode = ? and gg.czCode = ? and bm.zt = 1";
        List<Gg> ggList = (List<Gg>) this.find(hql, productCode.trim(), specCode.trim(), materialCode.trim());
        return ggList;
//        if (ggList != null && ggList.size() > 0)
//            return ggList.get(0);
//        else
//            return null;
    }

    //根据属性找出物资
    public Gg findByPro(String name, String ggxh, String cz) {
        String hql = "";
        List<Gg> ggList = null;
        if ("null".equals(cz) || StringUtils.isEmpty(cz)) {
            hql = "select gg from Gg gg,Bm bm where gg.code = bm.bmz and gg.name = ? and gg.ggxh = ? and gg.cz is null and bm.zt = 1";
            ggList = (List<Gg>) this.find(hql, name.trim(), ggxh.trim());
        } else {
            hql = "select gg from Gg gg,Bm bm where gg.code = bm.bmz and gg.name = ? and gg.ggxh = ? and gg.cz = ? and bm.zt = 1";
            ggList = (List<Gg>) this.find(hql, name.trim(), ggxh.trim(), cz);
        }
        if (ggList != null && ggList.size() > 0)
            return ggList.get(0);
        else
            return null;
    }

    public String findJzByPro(Gg gg, String cd) {
        String sql = "select j.jz from t_biz_jz j where j.pm = ? and j.cz = ? and j.gg = ? and j.cd like ?";
        List list = createSQLQuery(sql, gg.getName() == null ? "" : gg.getName(), gg.getCz() == null ? "" : gg.getCz(), gg.getGgxh() == null ? "" : gg.getGgxh(), '%' + cd.trim() + '%').list();
        return list.size() == 0 ? "" : list.get(0).toString();
    }

    //根据品名、规格、材质查出物资
    public Gg findByPro_2(String name, String ggxh, String cz) {
        String hql = "select gg from Gg gg where gg.name = ? and gg.ggxh = ? and gg.cz = ? ";
        List<Gg> ggList = (List<Gg>) this.find(hql, name.trim(), ggxh.trim(), cz.trim());
        if (ggList != null && ggList.size() > 0) {
            return ggList.get(0);
        } else {
            return null;
        }
    }


}
